import java.sql.*;
public class JDBCtest {
    public static void main(String[] args){
        Sql sql = new Sql();

        System.out.println("……插入数据……");
        Student stu1 = new Student("s001","老大",20,"计算机学院");
        Student stu2 = new Student("s002","老二",19,"计算机学院");
        Student stu3 = new Student("s003","老三",18,"计算机学院");
        Student stu4 = new Student("s004","老四",17,"计算机学院");
        sql.Insert(stu1);
        sql.Insert(stu2);
        sql.Insert(stu3);
        sql.Insert(stu4);
        sql.seletAll();

        System.out.println("……查询信息(所有)……");
        sql.seletAll();

        System.out.println("……删除记录……");
        sql.delete("s003");
        sql.seletAll();

        System.out.println("……删除记录(s003)……");
        sql.selectone("s003");

        System.out.println("……修改记录……");
        stu1.setCollege("通信学院");
        sql.update(stu1);
        sql.seletAll();

    }
}

//链接数据库
class JDBC{
    //注册驱动，只做一次
    //Driver接口，装载MySQL驱动
    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        }catch (Exception e){
            e.printStackTrace();
        }
    }
    //建立链接的方法
    //数据库test必须搭建好
    public static final String url="jdbc:mysql://localhost:3306/test?characterEncoding=utf8";
    public static final String user="root";
    public static final String password="mysql";
    public static Connection get_Connection() throws SQLException {
    Connection conn=DriverManager.getConnection(url,user,password);
    return conn;
    }
    //释放资源的方法
    //数据库链接Connection很耗资源，尽量晚创建，早释放
    //释放链接、创建执行statement、处理执行resultset占用的资源
    public static void get_Close(ResultSet rs,PreparedStatement ps,Connection conn){
        //加上try catch语句，以防前面关闭出错后面就不执行了
        try {
            if (rs != null){
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
            }catch (SQLException e) {
                e.printStackTrace();
            }finally {
                try {
                    if (conn != null) {
                        conn.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}
//相关SQL操作
class Sql{
    Connection conn=null;//链接
    PreparedStatement ps=null;//框架
    ResultSet rs=null;//处理执行
    //插入语句
    public void Insert(Student stu){
        try {
            conn=JDBC.get_Connection();//链接
            //SQl执行语句
            //预编译
            ps=conn.prepareStatement("insert into student(sno,name,age,college) values (?,?,?,?)");
            //传参数
            ps.setString(1, stu.getSno());
            ps.setString(2,stu.getName());
            ps.setInt(3,stu.getAge());
            ps.setString(4,stu.getCollege());
            //执行
            int i =ps.executeUpdate();
            if (i>=0){
                System.out.println("插入成功......");
            }
        } catch (SQLException e) {
            System.out.println("插入失败......");
            e.printStackTrace();
        }finally {
            try {
                JDBC.get_Close(rs,ps,conn);//关闭链接
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
    //查询语句(全部)
    public void seletAll(){
        try {
            conn=JDBC.get_Connection();
            ps=conn.prepareStatement("select * from student");
            rs=ps.executeQuery();//把查询结果全部存放进ResultSet类对象中
            while (rs.next()){
                System.out.println(
                        rs.getString(1)+","+
                                rs.getString(2)+","+
                                rs.getInt(3)+","+
                                rs.getString(4));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                JDBC.get_Close(rs,ps,conn);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
    //删除语句
    public void delete(String sno){
        try {
            conn=JDBC.get_Connection();
            ps=conn.prepareStatement("delete from student where sno=?");
            ps.setString(1,sno);
            int i=ps.executeUpdate();
            if (i>=0)
                System.out.println("删除成功......");
        } catch (SQLException e) {
            System.out.println("删除失败......");
            e.printStackTrace();
        }finally {
            try {
                JDBC.get_Close(rs,ps,conn);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
    //查询语句(某一个)
    public Student selectone(String sno){
        try {
            Student stu = new Student();
            conn=JDBC.get_Connection();
            ps= conn.prepareStatement("select * from student where sno=?");
            ps.setString(1,sno);
            rs= ps.executeQuery();
            while (rs.next()){
                stu.getSno();
                stu.getName();
                stu.getAge();
                stu.getCollege();
                return stu;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                JDBC.get_Close(rs,ps,conn);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return null;
    }
    //更新语句
    public void update(Student stu){
        try {
            conn=JDBC.get_Connection();
            ps= conn.prepareStatement("update student set college=? where sno=?");
            ps.setString(1,stu.getCollege());
            ps.setString(2,stu.getSno());
            int i= ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                JDBC.get_Close(rs,ps,conn);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

}
class Student{
    private String sno;
    private String name;
    private int age;
    private String college;
    public Student(){};
    public Student(String sno,String name,int age,String college){
        this.sno=sno;this.name=name;this.age=age;this.college=college;
    }

    public String getSno() {
        return sno;
    }

    public String getName() {
        return name;
    }

    public int getAge() {
        return age;
    }

    public String getCollege() {
        return college;
    }

    public void setCollege(String college) {
        this.college = college;
    }

    @Override
    public String toString() {
        return "Student{" +
                "sno='" + sno + '\'' +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", college='" + college + '\'' +
                '}';
    }
}